# -*- encoding: utf-8 -*-

import sqlite3
import os

def dict_factory(cursor, row):  
  # 重写row_factory方法，实现拉取字典结构数据
  d = {}  
  for idx, col in enumerate(cursor.description):  
    d[col[0]] = row[idx]  
  return d  

class dbconn():
  def __init__(self,dbname):   
    isNewDb = False if os.path.exists(dbname) else True
    connParms = self.ConnectDb(dbname)

    if connParms:
      self.conn,self.cur = connParms
      self.isConnect = True
      if isNewDb:self.InitDb() # 新数据库初始化数据
    else:
      self.isConnect = False
   
  def __del__(self):
    self.conn.close()

  def ConnectDb(self,db):    
    try:
      conn = sqlite3.connect(db, timeout=3, isolation_level=None,check_same_thread=False) 
      conn.row_factory = dict_factory
      cur = conn.cursor()     
      return conn,cur
    except BaseException as e:
      print("connectDb err:", e)
      return False

  def InsertRow(self, table, items):  
    # 新增记录
    # table:表格名称
    # items:字典数据
    fields = ','.join([x for x in items])
    values = ','.join([f"'{items[key]}'" for key in items])
    sql = f"INSERT INTO {table} ({fields}) VALUES ({values})"
    self.cur.execute(sql)
    self.conn.commit()

  def Query(self,scripts):  
    # 检索数据
    # scripts：检索语句
    # print(scripts)
    self.cur.execute(scripts)
    res = self.cur.fetchall()
    # print(res)   
    return res
    
  def InitDb(self):    
    # 初始化数据库，创建表格    
    """
    用户表：    user_info
    userId:     用户id
    loginName:  用户账号
    password:   登录密码
    trueName:   真实姓名
    roleId:     角色id
    avatar:     用户头像

    """
    sql = '''
    CREATE TABLE user_info
      (userId TEXT PRIMARY KEY NOT NULL,
      loginName TEXT NOT NULL,
      password TEXT NOT NULL,
      trueName TEXT NOT NULL,
      roleId TEXT NOT NULL,
      avatar  TEXT NOT NULL )
    '''
    self.cur.execute(sql)

    """用户分配角色： user_role ==============================
    roleId:     角色id
    roleName:   角色名称
    roleInfo:   角色描述信息
    """
    sql = '''
    CREATE TABLE user_role
      (roleId TEXT PRIMARY KEY NOT NULL,
      roleName TEXT NOT NULL,
      roleInfo TEXT NOT NULL)
    '''
    self.cur.execute(sql)

    """ 菜单项：menu_item =======================================
    menuId:       菜单id
    pid:          菜单父级id
    title:        菜单标题
    path:         菜单path
    name:         菜单name
    redirect:     路由重定向地址
    component:    路由组件
    alwaysShow:   始终显示
    hidden:       隐藏路由
    noCache:      不缓存路由
    activeMenu:   突出显示菜单
    icon:         路由图标
    type:         菜单类型
    sort:         菜单排序      """
    sql = '''
    CREATE TABLE menu_item(
      menuId TEXT PRIMARY KEY NOT NULL,
      pid TEXT NULL,
      title TEXT NULL,
      path TEXT NULL,
      name TEXT NULL,
      redirect TEXT NULL,
      component TEXT NULL,
      alwaysShow TEXT NULL,
      hidden TEXT NULL,
      noCache TEXT NULL,
      activeMenu TEXT NULL,
      icon TEXT NULL,
      type TEXT NULL,
      sort TEXT NULL
    )        '''
    self.cur.execute(sql)

    """ 角色分配菜单：menu_relation ==================================
    id:主键
    menuId:菜单ID
    roleId:角色ID
    """
    sql = '''
    CREATE TABLE menu_relation
      (id TEXT PRIMARY KEY NOT NULL,
      menuId TEXT NOT NULL,
      roleId TEXT NOT NULL
      )
    '''
    self.cur.execute(sql)
    self.conn.commit()  
    self.InitData()

  def InitData(self):
    # 初始化表格数据
    print("InitData=======================")
    tableList = ["menu_relation","user_info","user_role"]
    for table in tableList:

      with open(f"{table}.txt","r") as f:
        rows = f.readlines()
      keys = [v for v in rows[0].strip().split("\t")] 

      for row in rows[1:]:                  
        values = [v for v in row.strip().split("\t")]
        items = {keys[i]:values[i] for i in range(len(keys))}
        self.InsertRow(table,items)

    menus = [{
	"pid": "",
	"menuId": "96b58f5bf85ac41be4e2310fcdfc9cce",
	"path": "/permission",
	"component": "Layout",
	"redirect": "/permission/page",
	"alwaysShow": "True",
	"name": "Permission",
	"hidden": "",
	"title": "授权",
	"icon": "lock",
	"noCache": "",
	"activeMenu": ""
}, {
	"pid": "96b58f5bf85ac41be4e2310fcdfc9cce",
	"menuId": "8fddd565a7f4a9746887b22612a87db4",
	"path": "page",
	"component": "permission/page",
	"redirect": "",
	"alwaysShow": "",
	"name": "PagePermission",
	"hidden": "",
	"title": "页面授权",
	"icon": "",
	"noCache": "",
	"activeMenu": ""
}, {
	"pid": "96b58f5bf85ac41be4e2310fcdfc9cce",
	"menuId": "320133eb08e8952bbfb229290a9c08ba",
	"path": "directive",
	"component": "permission/directive",
	"redirect": "",
	"alwaysShow": "",
	"name": "DirectivePermission",
	"hidden": "",
	"title": "指令授权",
	"icon": "",
	"noCache": "",
	"activeMenu": ""
}, {
	"pid": "96b58f5bf85ac41be4e2310fcdfc9cce",
	"menuId": "b2f2bc759bdc631c9a0624a161e63722",
	"path": "role",
	"component": "permission/role",
	"redirect": "",
	"alwaysShow": "",
	"name": "RolePermission",
	"hidden": "",
	"title": "角色授权",
	"icon": "",
	"noCache": "",
	"activeMenu": ""
}, {
	"pid": "",
	"menuId": "1e2dac9999606adbb346314d299dc5d7",
	"path": "/icon",
	"component": "Layout",
	"redirect": "",
	"alwaysShow": "",
	"name": "",
	"hidden": ""
}, {
	"pid": "1e2dac9999606adbb346314d299dc5d7",
	"menuId": "267e6703e9e94ff5700d24d2cf40ff4c",
	"path": "index",
	"component": "icons/index",
	"redirect": "",
	"alwaysShow": "",
	"name": "Icons",
	"hidden": "",
	"title": "图标",
	"icon": "icon",
	"noCache": "True",
	"activeMenu": ""
}, {
	"pid": "",
	"menuId": "a39549152a079de7a4bc711c23e961c1",
	"path": "/example",
	"component": "Layout",
	"redirect": "/example/list",
	"alwaysShow": "",
	"name": "Example",
	"hidden": "",
	"title": "范例",
	"icon": "example",
	"noCache": "",
	"activeMenu": ""
}, {
	"pid": "a39549152a079de7a4bc711c23e961c1",
	"menuId": "c9256180dc0ee33700a9294becbe54d9",
	"path": "create",
	"component": "example/create",
	"redirect": "",
	"alwaysShow": "",
	"name": "CreateArticle",
	"hidden": "",
	"title": "新建文章",
	"icon": "edit",
	"noCache": "",
	"activeMenu": ""
}, {
	"pid": "a39549152a079de7a4bc711c23e961c1",
	"menuId": "a0f4530dce166bfef3d362b987a991c2",
	"path": "edit/\"\":id(\\d+)",
	"component": "example/edit",
	"redirect": "",
	"alwaysShow": "",
	"name": "EditArticle",
	"hidden": "True",
	"title": "编辑文章",
	"icon": "",
	"noCache": "True",
	"activeMenu": "/example/list"
}, {
	"pid": "a39549152a079de7a4bc711c23e961c1",
	"menuId": "8be704f3f0215a96ea936dce8c49de92",
	"path": "list",
	"component": "example/list",
	"redirect": "",
	"alwaysShow": "",
	"name": "ArticleList",
	"hidden": "",
	"title": "文章列表",
	"icon": "list",
	"noCache": "",
	"activeMenu": ""
}, {
	"pid": "",
	"menuId": "422f9e738aa0b6c15fc7891c7015e54d",
	"path": "/tab",
	"component": "Layout",
	"redirect": "",
	"alwaysShow": "",
	"name": "",
	"hidden": ""
}, {
	"pid": "422f9e738aa0b6c15fc7891c7015e54d",
	"menuId": "757014c893687fea59bbc08017f5bdf0",
	"path": "index",
	"component": "tab/index",
	"redirect": "",
	"alwaysShow": "",
	"name": "Tab",
	"hidden": "",
	"title": "分页",
	"icon": "tab",
	"noCache": "",
	"activeMenu": ""
}, {
	"pid": "",
	"menuId": "81dc98615eb38979cb2105685f3a0bd9",
	"path": "/error",
	"component": "Layout",
	"redirect": "noRedirect",
	"alwaysShow": "",
	"name": "ErrorPages",
	"hidden": "",
	"title": "错误页面",
	"icon": "404",
	"noCache": "",
	"activeMenu": ""
}, {
	"pid": "81dc98615eb38979cb2105685f3a0bd9",
	"menuId": "f186cb72f7f1eaf1c4d1ea59f95fb005",
	"path": "401",
	"component": "error-page/401",
	"redirect": "",
	"alwaysShow": "",
	"name": "Page401",
	"hidden": "",
	"title": "401",
	"icon": "",
	"noCache": "True",
	"activeMenu": ""
}, {
	"pid": "81dc98615eb38979cb2105685f3a0bd9",
	"menuId": "6319586dccfd17961c046cd5a662a401",
	"path": "404",
	"component": "error-page/404",
	"redirect": "",
	"alwaysShow": "",
	"name": "Page404",
	"hidden": "",
	"title": "404",
	"icon": "",
	"noCache": "True",
	"activeMenu": ""
}, {
	"pid": "",
	"menuId": "e0d274cda7404eb618c71644326b5da4",
	"path": "/error-log",
	"component": "Layout",
	"redirect": "",
	"alwaysShow": "",
	"name": "",
	"hidden": ""
}, {
	"pid": "e0d274cda7404eb618c71644326b5da4",
	"menuId": "1f5cdedad78dfab2f6fc02044e8fbc85",
	"path": "log",
	"component": "error-log/index",
	"redirect": "",
	"alwaysShow": "",
	"name": "ErrorLog",
	"hidden": "",
	"title": "错误日志",
	"icon": "bug",
	"noCache": "",
	"activeMenu": ""
}, {
	"pid": "",
	"menuId": "6c0e4b689a71ccb680d113edfe3f7541",
	"path": "/excel",
	"component": "Layout",
	"redirect": "/excel/export-excel",
	"alwaysShow": "",
	"name": "Excel",
	"hidden": "",
	"title": "Excel 操作",
	"icon": "excel",
	"noCache": "",
	"activeMenu": ""
}, {
	"pid": "6c0e4b689a71ccb680d113edfe3f7541",
	"menuId": "c2859c3c07be11407259139f14f520bd",
	"path": "export-excel",
	"component": "excel/export-excel",
	"redirect": "",
	"alwaysShow": "",
	"name": "ExportExcel",
	"hidden": "",
	"title": "Excel导出",
	"icon": "",
	"noCache": "",
	"activeMenu": ""
}, {
	"pid": "6c0e4b689a71ccb680d113edfe3f7541",
	"menuId": "589d3181e54cdcfaef4fd069e11fb92e",
	"path": "export-selected-excel",
	"component": "excel/select-excel",
	"redirect": "",
	"alwaysShow": "",
	"name": "SelectExcel",
	"hidden": "",
	"title": "Export选择",
	"icon": "",
	"noCache": "",
	"activeMenu": ""
}, {
	"pid": "6c0e4b689a71ccb680d113edfe3f7541",
	"menuId": "6834bebf3079dfcc000324cb73e1fd6c",
	"path": "export-merge-header",
	"component": "excel/merge-header",
	"redirect": "",
	"alwaysShow": "",
	"name": "MergeHeader",
	"hidden": "",
	"title": "Merge Header",
	"icon": "",
	"noCache": "",
	"activeMenu": ""
}, {
	"pid": "6c0e4b689a71ccb680d113edfe3f7541",
	"menuId": "2d39969480f502ddc180659dbe050c1b",
	"path": "upload-excel",
	"component": "excel/upload-excel",
	"redirect": "",
	"alwaysShow": "",
	"name": "UploadExcel",
	"hidden": "",
	"title": "导入 Excel",
	"icon": "",
	"noCache": "",
	"activeMenu": ""
}, {
	"pid": "",
	"menuId": "be80ecf861b74349d79c543039f4b259",
	"path": "/zip",
	"component": "Layout",
	"redirect": "/zip/download",
	"alwaysShow": "True",
	"name": "Zip",
	"hidden": "",
	"title": "Zip",
	"icon": "zip",
	"noCache": "",
	"activeMenu": ""
}, {
	"pid": "be80ecf861b74349d79c543039f4b259",
	"menuId": "1ae45c9c9d331286ceb2888cc9364492",
	"path": "download",
	"component": "zip/index",
	"redirect": "",
	"alwaysShow": "",
	"name": "ExportZip",
	"hidden": "",
	"title": "导出到 Zip",
	"icon": "",
	"noCache": "",
	"activeMenu": ""
}, {
	"pid": "",
	"menuId": "ebe75ddc1905fccd7db756ed38b6ff87",
	"path": "/pdf",
	"component": "Layout",
	"redirect": "/pdf/index",
	"alwaysShow": "",
	"name": "",
	"hidden": ""
}, {
	"pid": "ebe75ddc1905fccd7db756ed38b6ff87",
	"menuId": "d4a2627aa199b26c9a73af436d4fd47f",
	"path": "index",
	"component": "pdf/index",
	"redirect": "",
	"alwaysShow": "",
	"name": "PDF",
	"hidden": "",
	"title": "PDF相关",
	"icon": "pdf",
	"noCache": "",
	"activeMenu": ""
}, {
	"pid": "",
	"menuId": "b8f87e9d95e87fbe7668f090088d1775",
	"path": "/pdf/download",
	"component": "pdf/download",
	"redirect": "",
	"alwaysShow": "",
	"name": "",
	"hidden": "True"
}, {
	"pid": "",
	"menuId": "d8ab1a69ebbf6ddfddc4711ab7843888",
	"path": "/theme",
	"component": "Layout",
	"redirect": "",
	"alwaysShow": "",
	"name": "",
	"hidden": ""
}, {
	"pid": "d8ab1a69ebbf6ddfddc4711ab7843888",
	"menuId": "8bc50b9759f159d14497876efbb51394",
	"path": "index",
	"component": "theme/index",
	"redirect": "",
	"alwaysShow": "",
	"name": "Theme",
	"hidden": "",
	"title": "主题",
	"icon": "theme",
	"noCache": "",
	"activeMenu": ""
}, {
	"pid": "",
	"menuId": "df3d2ff243a3be1eb483978dc0b9344f",
	"path": "/clipboard",
	"component": "Layout",
	"redirect": "",
	"alwaysShow": "",
	"name": "",
	"hidden": ""
}, {
	"pid": "df3d2ff243a3be1eb483978dc0b9344f",
	"menuId": "4818b20b8320c5f6e478bb1d66e344de",
	"path": "index",
	"component": "clipboard/index",
	"redirect": "",
	"alwaysShow": "",
	"name": "ClipboardDemo",
	"hidden": "",
	"title": "粘贴板",
	"icon": "clipboard",
	"noCache": "",
	"activeMenu": ""
}]


    for menu in menus:
      self.InsertRow("menu_item",menu)
